package org.cjstudio.holypoopsgrs_protytpe.resource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.cjstudio.holypoopsgrs_protytpe.resource.Resources.UserProfileBook.UserProfile;
import org.cjstudio.holypoopsgrs_protytpe.resource.Resources.UserProfileBook.UserProfileResource;


public class UserProfileResourceMySQLImpl extends UserProfileResource {

	@Override
	public Long getUserId(String key) {
		Long userId = null;
		
		//TODO integrate with insert
		String query = "SELECT uid FROM holypoopsgrs.userprofilebook u WHERE u.key='" + key + "';";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.first()) {
				userId = rs.getLong("uid");
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userId;
	}

	@Override
	public void setName(Long id, String name) {
		String query = "UPDATE holypoopsgrs.userprofilebook (name) VALUES (" + name + ") WHERE uid=" + id + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public UserProfile getUserProfile(Long id) {
		UserProfile profile = null;
		
		String query = "SELECT name, message FROM holypoopsgrs.userprofilebook WHERE id=" + id + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.first()) {
				profile = new UserProfile(){};
				profile.id = id;
//				profile.key = null;
				profile.name = rs.getString("name");
				profile.message = rs.getString("message");
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return profile;
	}
	
	@Override
	public UserProfile setNameThenGetUserProfile(Long id, String name) {
		UserProfile profile = null;

		String setQuery = "UPDATE holypoopsgrs.userprofilebook SET name='" + name + "' WHERE uid=" + id + ";";
		System.out.println(setQuery);
		
		String getQuery = "SELECT name, message FROM holypoopsgrs.userprofilebook WHERE uid=" + id + ";";
		System.out.println(getQuery);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			
			Statement setStmt = conn.createStatement();
			setStmt.executeUpdate(setQuery);
			setStmt.close();
			
			Statement getStmt = conn.createStatement();
			ResultSet rs = getStmt.executeQuery(getQuery);
			if(rs.first()) {
				profile = new UserProfile(){};
				profile.id = id;
//				profile.key = null;
				profile.name = rs.getString("name");
				profile.message = rs.getString("message");
			}
			getStmt.close();
			
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return profile;
	}

	@Override
	public UserProfile getUserProfileByKey(String key) {
		UserProfile profile = null;
		
		//TODO integrate with insert
		String query = "SELECT uid, name, message FROM holypoopsgrs.userprofilebook u WHERE u.key='" + key + "';";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.first()) {
				profile = new UserProfile(){};
				profile.id = rs.getLong("uid");
//				profile.key = null;
				profile.name = rs.getString("name");
				profile.message = rs.getString("message");
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return profile;
	}

	@Override
	public Map<Long, UserProfile> getUserProfiles(List<Long> ids) {
		Map<Long, UserProfile> profiles = null;
		
		StringBuilder sb = new StringBuilder();
		for(int i=0; i<ids.size(); i++) {
			sb.append("uid=");
			sb.append(ids.get(i));
			if(i+1 < ids.size()) {
				sb.append(" OR ");
			}
		}
		
		String query = "SELECT uid, name, message FROM holypoopsgrs.userprofilebook WHERE " + sb.toString() + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.first()) {
				profiles = new TreeMap<Long, UserProfile>();
				do {
					UserProfile profile = new UserProfile(){};
					profile.name = rs.getString("name");
					profile.message = rs.getString("message");
					profiles.put(rs.getLong("uid"), profile);
				} while(rs.next());
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return profiles;
	}
	
	/* TEST TOOL */
	public void delete(Long id) {
		String query = "DELETE FROM holypoopsgrs.userprofilebook WHERE uid=" + id + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void deleteAll() {
		String query = "DELETE FROM holypoopsgrs.userprofilebook;";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
